﻿using Autofac;
using AutoMapper;
using FreeSql;
using KJSync.Access.Models;
using KJSyncXP.Access.Context;
using KJSyncXP.Access.Model;
using KJSyncXP.Extensions;
using KJSyncXP.Oracle.Context;
using KJSyncXP.Oracle.Model;
using KJSyncXP.Sqlite.Model;
using KJSyncXP.SQLite.Context;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Linq;
using System.Net.NetworkInformation;
using static log4net.Appender.RollingFileAppender;

namespace KJSyncXP.Sync.Services
{
    /// <summary>
    /// Access到Sqlite同步服务
    /// </summary>
    public class AccessSqliteSyncService
    {

        private readonly log4net.ILog _logger;

        private readonly AppConfig _appConfig;

        private CrcCalculator CrcCalculator = new CrcCalculator();

        private const string __REMOTE_SYNC_STATE_PENDING = "PENDING";

        private const string __REMOTE_SYNC_STATE_COMPLETE = "COMPLETE";

        ///<summary>
        /// 构造函数
        /// </summary>
        public AccessSqliteSyncService(log4net.ILog log, AppConfig appConfig)
        {
            _logger = log;
            _appConfig = appConfig;
        }

        IDbTransaction sqliteTran = null;
        IDbTransaction oracleTran = null;
        IDbTransaction accessTran = null;

        bool isAllSuccess = false;


        #region 同步标准数据

        /// <summary>
        /// 从Sqlite拉取同步标准数据到Access,反写oracle和sqlite,同时还要记录标准变更时间
        /// </summary>

        public void PullTestStandardToAccess()
        {
            var scope = App._container.BeginLifetimeScope();
            var sqliteCtx = scope.Resolve<SqliteDbContext>();
            var oracleCtx = scope.Resolve<OracleDbContext>();
            var accsseCtx = scope.Resolve<AccessDbContext>();

            try
            {
                //查询Access数据库Bas001中所有胶号
                var baslist = accsseCtx.Db.Queryable<Bas001>().ToList();

                //查询本地数据库KJ_TEST_STANDARD中所有还未同步的胶号数据
                var sqliteTestStandards = sqliteCtx.Db.Queryable<Sqlite_KJ_TEST_STANDARD>().
                    Where(item => item.RECEIVE_FLAG == __REMOTE_SYNC_STATE_PENDING).ToList();


                if (!sqliteTestStandards.Any())
                {
                    _logger.Warn("SQLite的KJ_TEST_STANDARD表中无数据，无需处理");
                    return;
                }

                // Access所有的胶号名
                var existingBasNames = baslist.Select(b => b.Name1?.Trim())
                                             .Where(name => !string.IsNullOrEmpty(name)).ToList();

                // Sqlite所有待同步的胶号名
                var existingPartNumber = sqliteTestStandards.Select(b => b.PART_NUMBER?.Trim())
                                             .Where(part => !string.IsNullOrEmpty(part)).ToList();

                // 1.新增的胶号
                //新增标准
                /*本地Access数据库中表BAS001的Name1与MES的KJ_TEST_STANDARD表的PART_NUMBER做对比。
                 * 如果KJ_TEST_STANDARD中存在，但BAS001中不存在，则视为新增该胶号标准：
                1、向BAS001表中插入新记录，获取IDNUM
                2、根据KJ_TEST_STANDARD的TEST_PROJECT，查询TAG001对应的TAGNUM以及缩放系数
                3、将IDNUM、TAGNUM、以及上下限保存至SET001*/
                var att = string.IsNullOrEmpty(sqliteTestStandards[0].PART_NUMBER);
                var btt = existingBasNames.Contains(sqliteTestStandards[0].PART_NUMBER);

                var addData = sqliteTestStandards
                    .Where(item => !string.IsNullOrEmpty(item.PART_NUMBER) && !existingBasNames.Contains(item.PART_NUMBER))
                    .Distinct()
                    .ToList();
                // 2.修改的胶号
                /* 本地Access数据库中表BAS001的Name1与MES的KJ_TEST_STANDARD表的PART_NUMBER做对比。
                 * 如果KJ_TEST_STANDARD、BAS001中都存在，但是KJ_TEST_STANDARD的创建时间 > BAS001的UDATE，
                 * 则视为更新该胶号标准：*/

                var updateData = sqliteTestStandards
                    .Where(sqliteItem =>
                        baslist.Any(basItem =>
                            basItem.Name1 == sqliteItem.PART_NUMBER &&  //条件1：两张表中都存在
                            sqliteItem.CREATION_DATE > basItem.Udate  // 条件2：MES 时间晚于 Access 更新时间
                        ))
                    .ToList();

                // 3.删除的胶号
                /* 本地Access数据库中表BAS001的Name1与MES的KJ_TEST_STANDARD表的PART_NUMBER做对比。
                 * 如果KJ_TEST_STANDARD中不存在，但BAS001中存在，则视为删除该胶号标准：
                 * 此外，当到达标准的失效时间后，也需要删除对应的标准*/

                // access里没有失效时间，所以需要从sqlite里判断所有失效的要删除的
                /* var deleteDateByDate = sqliteTestStandards.Where(item => item.END_DATE < DateTime.Now).ToList();
                 var needDeleteName = deleteDateByDate.Select(item => item.PART_NUMBER).ToList();
                 var deleteData = baslist
                     .Where(basitem => (!string.IsNullOrEmpty(basitem.Name1) && !existingPartNumber.Contains(basitem.Name1)) 
                     || needDeleteName.Contains(basitem.Name1))
                     .Distinct()
                     .ToList();*/
                
                
                accsseCtx.Db.Ado.Transaction(() =>
                {
                    var res = AddStandardToAccess(accsseCtx, addData);
                    accessTran = accsseCtx.Db.Ado.TransactionCurrentThread;

                    if (res)
                    {
                        sqliteCtx.Db.Ado.Transaction(() =>
                        {
                            sqliteCtx.Db.Update<Sqlite_KJ_TEST_STANDARD>().Where(item => item.RECEIVE_FLAG == "PENDING" && existingPartNumber.Contains(item.PART_NUMBER))
                            .Set(item => item.RECEIVE_FLAG, "COMPLETE")
                            .ExecuteAffrows();
                            // 在回调内，通过 TransactionCurrentThread 获取当前线程的 SQLite 事务
                            // 这里暂不执行具体业务，仅捕获事务对象
                            sqliteTran = sqliteCtx.Db.Ado.TransactionCurrentThread;
                        });

                        oracleCtx.Db.Ado.Transaction(() => {
                            oracleCtx.Db.Update<KJ_TEST_STANDARD>()
                            .Where(item => item.RECEIVE_FLAG == "PENDING" && existingPartNumber.Contains(item.PART_NUMBER))
                            .Set(item => item.RECEIVE_FLAG, "COMPLETE")
                            .ExecuteAffrows();
                            oracleTran = oracleCtx.Db.Ado.TransactionCurrentThread;
                        });

                        // 所有操作无异常，标记“全部成功”
                        isAllSuccess = true;
                    }

                });               
            }
            catch (Exception ex)
            {

                _logger.Error("同步标准到access异常",ex);
            }
            /*finally
            {
                try
                {
                    if (isAllSuccess)
                    {
                        accessTran?.Commit(); // 提交 Access
                        sqliteTran?.Commit(); // 提交sqlite
                        oracleTran?.Commit(); // 提交 Oracl

                    }
                    else
                    {
                        accessTran?.Rollback(); // 回滚 Access
                        oracleTran?.Rollback(); // 回滚 Oracle
                        sqliteTran?.Rollback(); // 回滚 SQLite
                    }
                }
                finally
                {
                    // 释放事务资源（必做，避免连接泄漏）
                    accessTran?.Dispose();
                    sqliteTran?.Dispose();
                    oracleTran?.Dispose();
                }
            }*/
        }

        /// <summary>
        /// 处理新增、修改、删除的胶号标准
        /// </summary>

        public bool AddStandardToAccess(AccessDbContext accsseCtx, List<Sqlite_KJ_TEST_STANDARD> addData)
        {
            try
            {
                // 先查询Bas001表当前最大Idnum，避免循环内重复查询
                int maxBasId = accsseCtx.Db.Queryable<Bas001>().Max(b => b.Idnum);
                foreach (var item in addData)
                {
                    // 生成新主键：最大ID+1（确保自增唯一性）
                    int newBasId = maxBasId + 1;

                    var bas001 = new Bas001();
                    bas001.Idnum = newBasId;
                    bas001.Name1 = item.PART_NUMBER;
                    bas001.Cdate = DateTime.Now;
                    bas001.Udate = DateTime.Now;


                    // 插入Bas001表
                    accsseCtx.Db.Insert(bas001).ExecuteAffrows();
                    //transaction.Insert(bas001).ExecuteAffrows();

                    // 更新最大ID，为下一条数据准备
                    maxBasId = newBasId;

                    // 根据TEST_PROJECT查询对应的Tag001记录
                    var tag001 = accsseCtx.Db.Queryable<Tag001>()
                        .Where(t => t.Tagnum.ToString() == item.TEST_PROJECT)
                        .First();

                    if (tag001 != null)
                    {
                        var set001 = new Set001()
                        {
                            Tagnum = tag001.Tagnum,
                            Idnum = bas001.Idnum, // 关联Bas001的自定义自增ID
                            Ltol = (int?)item.MIN_DATA,
                            Utol = (int?)item.MAX_DATA,
                        };

                        // 插入Set001表
                        accsseCtx.Db.Insert(set001).ExecuteAffrows();
                        //transaction.Insert(set001).ExecuteAffrows();
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }

        }

        #endregion

        #region 获取硫变仪数检测据到sqlite

        public void GetAccessDat200RhToSqlite()
        {
            try
            {
                _logger.Info("开始从Access拉取Dat200Rh数据到SQLite中间表");

                using (var scope = App._container.BeginLifetimeScope())
                {
                    var accsseCtx2 = scope.Resolve<AccessDbContext2>();
                    var sqliteCtx = scope.Resolve<SqliteDbContext>();
                  
                    /*DateTime dateTime = DateTime.Now.Date;
                    DateTime dateTime2 = dateTime.AddDays(-1);*/
                    DateTime dateTime = DateTime.Parse("2025/02/21");
                    DateTime dateTime2 = DateTime.Parse("2025/02/22");
                    var dat200RhList = accsseCtx2.Db.Queryable<Dat200Rh>()
                        .Where(item => item.F1 == dateTime || item.F1 == dateTime2)
                        .ToList();
                    
                    
                    if (!dat200RhList.Any())
                    {
                        _logger.Info($"Access中Dat200Rh没有{dateTime},{dateTime2}数据，无需同步");
                        return;
                    }
                    else
                    {
                        // 入侵access，增加hash值字段简便操作
                        var accessDataWithHash = dat200RhList
                             .Select(item =>
                             {
                                 var json = JsonSerializeHelper.SerializeObjectToJson(item);
                                 var crc = CrcCalculator.ComputeCrc128(json).ToString();
                                 item.Hash = crc;
                                 return item;
                             })
                             .ToList();

                        // 获取SQLite中对应日期的数据
                        var existingInSqlite = sqliteCtx.Db.Queryable<SqliteDat200Rh>()
                            .Where(item => item.F1 == dateTime || item.F1 == dateTime2)
                            .ToList();

                        // 新增 Access存在而SQLite不存在的记录 (曲线文件 + 时间)
                        var newData = accessDataWithHash
                             .Where(access => !existingInSqlite.Any(sqlite =>
                                 sqlite.F0 == access.F0))
                             .ToList();

                        //修改 Access和SQLite都存在但哈希值不同的记录
                        /*var updatedData = accessDataWithHash
                            .Where(access => existingInSqlite.Any(sqlite =>
                                sqlite.F0 == access.F0 &&
                                sqlite.F1 == access.F1 &&
                                sqlite.F2 == access.F2 &&
                                sqlite.SourceHash != access.Hash
                                ))
                            .ToList();*/

                        // 执行同步操作
                        var newEntities = ProcessNewData(newData);
                        //var updatedEntities = ProcessUpdatedData(updatedData);

                        // 保存变更(新增+修改)

                        if (newEntities.Any())
                        {
                            sqliteCtx.Db.Insert(newEntities).ExecuteAffrows();
                            _logger.Info($"拉取Dat200Rh数据到SQLite同步完成 - 新增: {newEntities.Count}");
                        }
                        else
                        {
                            _logger.Info("没有需要新增到Access的Dat200Rh数据");
                        }
                        /*if (updatedEntities.Any()) {

                            var add = sqliteCtx.Db.Update<SqliteDat200Rh>()
                                .SetSource(updatedEntities,a=>a.F0)
                                .ExecuteAffrows();                   
                        }*/
                        //_logger.Info($"Dat200Rh数据同步完成 - 新增: {newEntities.Count}, 修改: {updatedEntities.Count}");
                       
                    }                    
                }
            }
            catch (Exception ex)
            {
                _logger.Error("拉取Dat200Rh数据到SQLite失败",ex);
            }
        }



        /// 处理新增数据
        /// </summary>
        /// <param name="newData">需要新增的数据列表</param>
        /// <returns>转换后的SQLite实体列表</returns>
        private List<SqliteDat200Rh> ProcessNewData(List<Dat200Rh> newData)
        {

            AutoMapperConfig.Init();
            var newEntities = Mapper.Map<List<SqliteDat200Rh>>(newData);
            foreach (var item in newEntities)
            {
                string item1 = item.F1?.ToString("yyyy-MM-dd"); // 年月日部分
                string item2 = item.F2?.ToString("HH:mm:ss");   // 时分秒部分

                item.RECEIVE_FLAG = __REMOTE_SYNC_STATE_PENDING;
                item.RECEIVE_DATE = DateTime.Now;
                item.SourceHash = item.Hash;
                item.SourceDataJson = JsonSerializeHelper.SerializeObjectToJson(item);
                item.CREATE_DATE = DateTime.Parse(item1 + " "+ item2);
            }

            return newEntities;
        }

        /// <summary>
        /// 处理更新数据
        /// </summary>
        /// <param name="updatedData">需要更新的数据列表</param>
        /// <param name="existingInSqlite">SQLite中已存在的实体</param>
        /// <returns>更新后的实体列表</returns>
        private List<SqliteDat200Rh> ProcessUpdatedData(List<Dat200Rh> updatedData)
        {
            AutoMapperConfig.Init();
            var updatedEntities = Mapper.Map<List<SqliteDat200Rh>>(updatedData);

            foreach (var item in updatedEntities)
            {
                item.RECEIVE_FLAG = __REMOTE_SYNC_STATE_PENDING;
                item.RECEIVE_DATE = DateTime.Now;
                item.SourceHash = item.Hash;
                item.SourceDataJson = JsonSerializeHelper.SerializeObjectToJson(item);
            }

            return updatedEntities;
        }


        #endregion

    }
}